package com.scalar.chiptrack.adaptor.sitime;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.DateUtil;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 8 Dec, 2010
 * Time: 5:14:21 PM
 * To change this template use File | Settings | File Templates.
 */
public class CustomerDataParser extends JobHandler implements AdaptorConstants
{
    private Logger m_parserLogger       = null;
    private int adaptor_id = -1;
    public  CustomerDataParser() throws ScalarException
    {
    }
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new CustomerDataParser().execute();
        }
        catch ( Exception e )
        {
            throw e;
        }
    }
    
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader( "CUSTOMER", CONSTANT_PARSER );
        boolean success = false;
        String lastParsedfileDate   = AdaptorRunInfo.getAdaptorLastParsedFileDate( "CUSTOMER" );

        if ( lastParsedfileDate == null )
        {
            lastParsedfileDate = DateUtil.getPreviousDate(10);
        }

		PreparedStatement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		Connection conn1 = null;
		GPCustomer customer = null;
		Address address = null;
		ArrayList<Address> addressList = new ArrayList<Address>(); 
		ArrayList<GPCustomer> customerList = new ArrayList<GPCustomer>();
		ArrayList<GPCustomer> customerARList = new ArrayList<GPCustomer>();
		try
        {
			conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_GREAT_PLAINS );
            System.out.println("connected");
    		stmt = conn.prepareStatement( "SELECT CUSTNMBR, CUSTNAME, CUSTCLAS, CNTCPRSN, SHIPMTHD, PRBTADCD, PRSTADCD, PYMTRMID, CRLMTAMT, COMMENT1, HOLD FROM RM00101 WHERE INACTIVE = 0 AND ( MODIFDT > ? OR CREATDDT > ? )" );
    		stmt.setString( 1, lastParsedfileDate  );
    		stmt.setString( 2, lastParsedfileDate  );
    		rs = stmt.executeQuery();
    
    		// Iterate through the data in the result set and display it.
    		while ( rs.next() ) 
    		{
    			customer = new GPCustomer();
    			customer.setCustomerNumber( rs.getString( "CUSTNMBR" ) );
    			customer.setCustomerName  ( rs.getString( "CUSTNAME" ) );
    			customer.setDefaultBillingAddressID( rs.getString( "PRBTADCD" ) );
    			customer.setDefaultShippingAddressID( rs.getString( "PRSTADCD" ) );
    			customer.setPaymentTerms( rs.getString( "PYMTRMID" ) );
    			customer.setCreditLimit( rs.getInt( "CRLMTAMT" ) );
    			customer.setCustomerHold( rs.getInt( "HOLD" ) );
    			customerList.add( customer );
    		}
    		
    		System.out.println("Customer Count " + customerList.size());

    		if (rs != null) try { rs.close(); } catch(Exception e) {}
    		if (stmt != null) try { stmt.close(); } catch(Exception e) {}
    		
    		stmt = conn.prepareStatement( "SELECT CUSTNMBR, ADRSCODE, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3,  COUNTRY, CITY, STATE, ZIP, PHONE1, FAX FROM RM00102 WHERE ( MODIFDT > ? OR CREATDDT > ? )" );
    		stmt.setString( 1, lastParsedfileDate  );
    		stmt.setString( 2, lastParsedfileDate  );
    		rs = stmt.executeQuery();
    		while ( rs.next() ) 
    		{
    			address = new Address();
    			address.setEpicoreShipCode( rs.getString( "CUSTNMBR" ) );
    			address.setTagName ( rs.getString( "ADRSCODE" ) );
    			address.setContactPerson( rs.getString( "CNTCPRSN" ) );
    			address.setAddress1( rs.getString( "ADDRESS1" ) );
    			address.setAddress2( rs.getString( "ADDRESS2" ) );
    			address.setAddress3( rs.getString( "ADDRESS3" ) );
    			address.setCountry ( rs.getString( "COUNTRY" ) );
    			address.setCity    ( rs.getString( "CITY" ) );
    			address.setState   ( rs.getString( "STATE" ) );
    			address.setZipCode ( rs.getString( "ZIP" ) );
    			address.setPhoneNumber( rs.getString( "PHONE1" ) );
    			address.setFaxNumber( rs.getString( "FAX" ) );
    			addressList.add( address );
    		}
    		System.out.println("addressList Count " + addressList.size());
    		
    		if (rs != null) try { rs.close(); } catch(Exception e) {}
    		if (stmt != null) try { stmt.close(); } catch(Exception e) {}
    		System.out.println("Fetching customer AR Balance");
    		
    		stmt = conn.prepareStatement( "select RM00103.CUSTNMBR, RM00103.CUSTBLNC FROM RM00103, RM00101 WHERE RM00103.CUSTNMBR = RM00101.CUSTNMBR AND RM00101.INACTIVE = 0" );
    		rs = stmt.executeQuery();
    		while ( rs.next() ) 
    		{
    			customer = new GPCustomer();
    			customer.setCustomerNumber( rs.getString( "CUSTNMBR" ) );
    			customer.setCustomerAR( rs.getDouble( "CUSTBLNC" ) );
    			customerARList.add( customer );
    		}
            success = true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            success = false;
        }
		finally 
		{
			if (rs != null) try { rs.close(); } catch(Exception e) {}
	    		if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	    		if (conn != null) try { conn.close(); } catch(Exception e) {}
		}
		
		System.out.println("Updating Customers list ..");
		try
		{
			conn1 = DataSource.getConnection();
			stmt = conn1.prepareStatement( " {call SP_CREATE_UPDATE_CUSTOMER_SITIME(?,?,?,?,?,?,?)}" );
			int index = 1;
			if( customerList != null && customerList.size() > 0 )
			{
				for( GPCustomer gpcustomer:customerList )
				{
					index = 1;
					stmt.setString( index++ , gpcustomer.getCustomerNumber().trim() );
					stmt.setString( index++ , gpcustomer.getCustomerName().trim()  );
					stmt.setString( index++ , gpcustomer.getDefaultBillingAddressID()  );
					stmt.setString( index++ , gpcustomer.getDefaultShippingAddressID() );
					stmt.setString( index++ , gpcustomer.getPaymentTerms().trim() );
					stmt.setInt( index++ , gpcustomer.getCreditLimit() );
					stmt.setInt( index++ , gpcustomer.getCustomerHold() );
					stmt.execute();
				}
			}
			if (stmt != null) try { stmt.close(); } catch(Exception e) {}
			stmt = conn1.prepareStatement( " {call SP_CREATE_UPDATE_ADDRESS_SITIME(?,?,?,?,?,?,?,?,?,?,?,?)}" );
			if( addressList != null && addressList.size() > 0 )
			{
				for( Address gpaddress:addressList )
				{
					index = 1;
					stmt.setString( index++ , gpaddress.getEpicoreShipCode().trim() );
					stmt.setString( index++ , gpaddress.getTagName().trim()  );
					stmt.setString( index++ , gpaddress.getAddress1().trim()  );
					stmt.setString( index++ , gpaddress.getAddress2().trim() );
					stmt.setString( index++ , gpaddress.getAddress3().trim() );
					stmt.setString( index++ , gpaddress.getCountry().trim() );
					stmt.setString( index++ , gpaddress.getCity().trim() );
					stmt.setString( index++ , gpaddress.getState().trim() );
					stmt.setString( index++ , gpaddress.getZipCode().trim() );
					stmt.setString( index++ , gpaddress.getPhoneNumber().trim() );
					stmt.setString( index++ , gpaddress.getFaxNumber().trim() );
					stmt.setString( index++ , gpaddress.getContactPerson().trim() );
					stmt.executeUpdate();
				}				
			}
			if (stmt != null) try { stmt.close(); } catch(Exception e) {}
			
			stmt = conn1.prepareStatement( "UPDATE CUSTOMER SET AR_BALANCE = ? WHERE C_NUMBER = ?" );
			if( customerARList != null && customerARList.size() > 0 )
			{
				for( GPCustomer gpcustomer:customerARList )
				{
					stmt.setDouble( index++ , gpcustomer.getCustomerAR() );
					stmt.setString( index++ , gpcustomer.getCustomerNumber().trim() );
					stmt.executeUpdate();
				}
			}
            success = true;
		}
		catch(Exception e)
		{
			e.printStackTrace();
            success = false;
		}
		finally 
		{
            if (conn1 != null)
            {
            	System.out.println("Commiting the info");
            	conn1.commit();
            }
			if (rs != null) try { rs.close(); } catch(Exception e) {}
    		if (stmt != null) try { stmt.close(); } catch(Exception e) {}
    		if (conn1 != null) try { conn1.close(); } catch(Exception e) {}
		}

        if ( success)
        {
            adaptor_id = AdaptorRunInfo.saveAdaptorLastUpdatedStatus( CONSTANT_ADAPTOR, "CUSTOMER", CONSTANT_SUCCESS, -1);
            AdaptorRunInfo.updateLastRunFileDate( adaptor_id , DateUtil.getCurrentDate() );
        }

		System.out.println("Customer information updated Successfully...");
		
        /*
        File doneDestDir = new File( m_dataOutputDir + "/done" );
        File dataoutDir  = new File( m_dataOutputDir );

        File[] fileList = dataoutDir.listFiles();

        if ( ( fileList == null ) || (fileList.length <= 3))
        {
            log("No file found in directory: " + dataoutDir);
            System.out.println("No file found in directory: " + dataoutDir );
            throw new ScalarException("No file found in directory: " + dataoutDir+ "----\n\n" );
        }

        ArrayList<File> successFileList = new ArrayList<File>();

        boolean success = false;

        for ( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() )
            {
                continue;
            }

            try
            {
                if ( fileList[i].getName().startsWith( CustomerAdaptor.FILE_NAME )  || fileList[i].getName().indexOf( CustomerAdaptor.FILE_NAME )  != -1)
                {

                    updateCustomerNoData( fileList[i].getAbsolutePath() );

                    updateAddressTagName( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }

                success = true;
            }
            catch (Exception e)
            {
                success = false;
                log("Exception while parsing file: " + fileList[i].getAbsolutePath() + "\nException: " + e);
                e.printStackTrace();
                throw e;
            }
        }

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( POAdaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);
        // transaction 9
        try
        {
            if (success)
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, POAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println("SUCCESSFULLY UPDATED IN THE DATABASE");
            }
        }
        catch (ScalarException se)
        {
            success = false;  //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, POAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log("WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se);
            throw se;
        }

        // Step 10
        // Move the successful files to 'done' directory
        if (success)
        {
            AdaptorUtils.moveFilesToDone( successFileList, doneDestDir);
        }*/
    }

    private static void updateCustomerNoData( String  fileName ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        File detailWipFile = new File( fileName );
        if ( !detailWipFile.exists() )
        {
            throw new ScalarException( fileName + " doesnt exist" );
        }

        Parser parser = ParserFactory.getXLSParser( detailWipFile, "Customer Master" );

        ((XLSParserImpl)parser).setHeaderRowIdx(-1);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {
            pstmt = conn.prepareStatement( " { CALL sp_create_update_CustomerNo_Data (?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( 0 ) != null && result.getString ( 0 ).trim().length() > 0  )
                {
                    pstmt.setString( 1, result.getString ( 0 ).trim() );
                    pstmt.setString( 2, result.getString ( 1).trim() );
                    //System.out.println("Customer Name " + result.getString ( 1 ).trim() + "   " +result.getString ( 0 ).trim() );
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
     private static void updateAddressTagName( String fileName ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        File detailWipFile = new File( fileName );
        if ( !detailWipFile.exists() )
        {
            throw new ScalarException( fileName + " doesnt exist" );
        }

        Parser parser = ParserFactory.getXLSParser( detailWipFile, "Customer Address Master" );

        ((XLSParserImpl)parser).setHeaderRowIdx(-1);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {
            pstmt = conn.prepareStatement( " { CALL sp_create_update_Customer_Address_Tag ( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( 0 ) != null && result.getString ( 0 ).trim().length() > 0  )
                {
                    pstmt.setString( 1, result.getString ( 0 ).trim() );
                    pstmt.setString( 2, result.getString ( 3 ).trim() );
                    pstmt.setString( 3, result.getString ( 4 ).trim() );
                    pstmt.setString( 4, result.getString ( 6 ).trim() );
                    pstmt.setString( 5, result.getString ( 7 ).trim() );
                    pstmt.setString( 6, result.getString ( 8 ).trim() );
                    pstmt.setString( 7, result.getString ( 9 ).trim() );
                    pstmt.setString( 8, result.getString ( 1 ).trim() );

                    pstmt.addBatch();

                }
                else
                {
                    //System.out.println ( "IN ELSE " + result.getString ( "Wafer Part" ) + ":" +  result.getString ( "Sort" )  );
                }
            }
            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    private void log( String message )
    {
      if ( message == null ) return;

      if ( m_parserLogger != null )
      {
          m_parserLogger.logDebug( message + LINE_SEPARATOR );
      }
      else
      {
          System.out.println( message );
      }
    } // end of log method
}

